package com.bg.sparkproject.utils;

import com.bg.sparkproject.config.ConfigurationManager;
import com.bg.sparkproject.constant.Constants;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * @Auther: zhengshunzhi
 * @Date: 2018/12/18 16:05
 * @Description: JDBC工具类
 */
public class JDBCHelper {

    //1：加载驱动
    static {
        try {
            String driver = ConfigurationManager.getProperty(Constants.JDBC_DRIVER);
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //单例化JDBCHelper
    private static JDBCHelper jdbcHelper = null;

    //创建数据库连接池
    private LinkedList<Connection> dataSource = new LinkedList<>();

    /**
     * 功能描述: 私有化构造方法
     *
     * @auther: bonree
     * @date: 2018/12/18 16:45
     */
    private JDBCHelper() {
        //获取数据库连接池的大小
        int dataSourceSize = ConfigurationManager.getInteger(Constants.JDBC_DATASOURCE_SIZE);
        String url = ConfigurationManager.getProperty(Constants.JDBC_URL);
        String user = ConfigurationManager.getProperty(Constants.JDBC_USER);
        String pwd = ConfigurationManager.getProperty(Constants.JDBC_PASSWORD);

        for (int i = 0; i < dataSourceSize; i++) {
            try {
                Connection connection = DriverManager.getConnection(url, user, pwd);
                dataSource.push(connection);
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

    /*
     *获取单例
     */
    public static JDBCHelper getInstance() {
        if (jdbcHelper == null) {
            synchronized (JDBCHelper.class) {
                if (jdbcHelper == null) {
                    jdbcHelper = new JDBCHelper();
                }
            }
        }
        return jdbcHelper;
    }

    /**
     * 功能描述: 获取连接
     *
     * @param:
     * @return:
     * @auther: bonree
     * @date: 2018/12/18 16:41
     * 有可能获取连接的时候连接池中是空的，所以我们需要简单的处理一下
     * 循环判断 等待机制
     * 加锁防止多线程无限等待
     */
    public synchronized Connection getConnection() {
        while (dataSource.size() == 0) {
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        return dataSource.poll();
    }

    /**
     * 功能描述: 执行增删改的sql语句
     *
     * @param: sql语句
     * @param: params参数
     * @return: 影响行数
     * @auther: bonree
     * @date: 2018/12/18 16:50
     */
    public int executeUpdate(String sql, Object[] params) {
        Connection connection = null;
        PreparedStatement pst = null;
        int res = 0;
        try {
            connection = getConnection();
            pst = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            res = pst.executeUpdate();

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (connection != null) {
                dataSource.push(connection);
            }

            colsePreparedStatement(pst);
        }
        return res;
    }

    /**
     * 功能描述: 执行sql查询语句
     *
     * @param:
     * @return:
     * @auther: bonree
     * @date: 2018/12/18 17:08
     */
    public void excuteQuery(String sql, Object[] params, QueryCallBack callBack) {
        Connection connection = null;
        PreparedStatement pst = null;
        ResultSet rst = null;
        try {
            connection = getConnection();
            pst = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                pst.setObject(i + 1, params[i]);
            }
            rst = pst.executeQuery();
            callBack.process(rst);
        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (connection != null) {
                dataSource.push(connection);
            }

            colsePreparedStatement(pst);
        }
    }

    /**
     * 功能描述: 回调查询接口
     *
     * @param:
     * @return:
     * @auther: bonree
     * @date: 2018/12/18 17:05
     */
    public static interface QueryCallBack {
        //处理查询结果
        void process(ResultSet rst) throws SQLException;
    }

    /**
     * 功能描述: 关闭PreparedStatement
     *
     * @param:
     * @return:
     * @auther: bonree
     * @date: 2018/12/18 17:12
     */
    public void colsePreparedStatement(PreparedStatement pst) {
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 功能描述: 批量执行sql
     *
     * @param:
     * @return:
     * @auther: bonree
     * @date: 2018/12/18 17:31
     * jdbc高级api
     */
    public int[] excuteBatch(String sql, List<Object[]> paramsList) {
        int[] rtn = null;
        Connection connection = null;
        PreparedStatement pst = null;

        try {
            connection = getConnection();
            //将自动提交设置为false
            connection.setAutoCommit(false);
            pst = connection.prepareStatement(sql);

            for (Object[] paras : paramsList) {
                for (int i = 0; i < paras.length; i++) {
                    pst.setObject(i + 1, paras[i]);
                }
                pst.addBatch();
            }

            //执行批量sql
            rtn = pst.executeBatch();
            //提交
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            if (connection != null){
                dataSource.add(connection);
            }
            colsePreparedStatement(pst);
        }
        return rtn;
    }
}
